
* SS income for whites
* Tables 76, 77, 79 and exports parameters
qui{
clear 
use "${data}/HRS_Analysis.dta"
*keep alive and dead: 1 and 4 are alive and 5 and 6 are dead
keep if iwstatr==1 | iwstatr==4 

* keep Whites
drop if hispanic==1 // drop Hispanics
drop if race==2 // drop Blacks
rename iwendmr month // interview end date month

gen educ_group=1 if raedyrs <=12
replace educ_group=2 if raedyrs>=13 & raedyrs<16
replace educ_group=3 if raedyrs>=16 & raedyrs!=.

keep if gender==0  // keep males 0=male, 1=women


* wage at ages 60-64 if working
gen wage_60 = wgihrr if age>=60 & age<=64 & (lbrfr==1 | lbrfr==2)
bysort ID: egen wage_60_64 = mean(wage_60)
replace wage_60_64=. if wage_60_64>100 // eliminate extremes
drop wage_60

* years worked at ages 64-65
gen yrs_worked = work_yrs_r if age==64 | age==65
bysort ID: egen  yrs_worked_64_65 = mean(yrs_worked)
drop yrs_worked 

keep if age>65
tabstat isretr, stat(min p10 p25 p50 p75 p90 p95 p99 max mean)
tabstat ipenar, stat(min p10 p25 p50 p75 p90 p95 p99 max mean) // meadian and mean very different

gen SS_income = isretr+ ipenar
keep if lbrfr==5 // keep if retired 

order age ID age married SS_income isretr ipenar work_yrs_r

tabstat ipenar, stat(p25 p50 p90 p95 p99 max)


* we care about years worked at age 65
* for each ID, we choose their experience at the first year observed after 65, if getting SS
sort ID wave
replace work_yrs_r = work_yrs_r[_n-1] if ID==ID[_n-1] 


*tab work_yrs_r if age>64
gen work_yrs_r_cat = 1 if work_yrs_r>=0 & work_yrs_r<30
replace work_yrs_r_cat = 2 if work_yrs_r>=30 & work_yrs_r<38
replace work_yrs_r_cat = 3 if work_yrs_r>=38 & work_yrs_r!=. //& work_yrs_r<43


**** Table 77 - distribution of experience groups. - copy paste into table
tab work_yrs_r_cat if age<70 // this gives distribution for table in appendix. 
* let's also do by educ_group
bysort educ_group: tab work_yrs_r_cat if age<70

* only count each person once. collapse by ID
collapse (mean) wage_60_64 yrs_worked_64_65 SS_income isretr  (min) educ_group work_yrs_r_cat work_yrs_r, by(ID)

drop if SS_income<0 | SS_income==.

* Table 76 - first row
table  educ_group , statistic(mean SS_income) statistic(p50 SS_income)


tab work_yrs_r_cat
egen tercile=xtile(SS_income), by(educ_group work_yrs_r_cat) n(3) 


collapse (p50) SS_income , by(educ_group work_yrs_r_cat tercile)
preserve 
* Table 79 - median SS 
reshape wide SS_income, i(educ_group work_yrs_r_cat) j(tercile) 
restore

sort educ_group tercile work_yrs_r_cat
drop educ_group tercile work_yrs_r_cat
replace SS_income =SS_income/5200
outsheet using "${data_model}/SS_inc.txt", nolabel nonames replace	

}

	
* SPOUSES' INCOME 	
* Table 76 - second row.
qui{	
clear 
use "${data}/HRS_Analysis.dta"
*keep alive and dead: 1 and 4 are alive and 5 and 6 are dead
keep if iwstatr==1 | iwstatr==4 
* keep Whites
drop if hispanic==1 // drop Hispanics
drop if race==2 // drop Blacks

gen educ_group=1 if raedyrs <=12
replace educ_group=2 if raedyrs>=13 & raedyrs<16
replace educ_group=3 if raedyrs>=16 & raedyrs!=.

keep if gender==0  // keep males 0=male, 1=women

keep if age>65 & married==1
gen SS_income_spouse = isrets+ ipenas


collapse (mean) SS_income_spouse (min) educ_group , by(ID)


* Table 76 - second row. 
table educ_group, stat(mean SS_income_spouse) stat(p50 SS_income_spouse)
collapse (mean) SS_income_spouse, by(educ_group)
sort educ_group
replace SS_income_spouse=SS_income_spouse/5200 //  model units

drop educ_group
outsheet using "${data_model}/Income_spouse_ret.txt", nolabel nonames replace	
}


** SS income for Minorities
* Table 125
qui{
clear 
use "${data}/HRS_Analysis.dta"
keep if iwstatr==1 | iwstatr==4 

gen race_use=2 if race==2
replace race_use=3 if hispanic==1 
replace race_use=1 if race_use!=2 & race_use!=3

gen educ_group=1 if raedyrs <=12
replace educ_group=2 if raedyrs>=13 & raedyrs<16
replace educ_group=3 if raedyrs>=16 & raedyrs!=.
keep if educ_group==1

keep if gender==0  // keep males 0=male, 1=women
rename iwendmr month // interview end date month

tab race_use

* wage at ages 60-64 if working
gen wage_60 = wgihrr if age>=60 & age<=64 & (lbrfr==1 | lbrfr==2)
bysort ID: egen wage_60_64 = mean(wage_60)
replace wage_60_64=. if wage_60_64>100 // eliminate extremes
drop wage_60

* years worked at ages 64-65
gen yrs_worked = work_yrs_r if age==64 | age==65
bysort ID: egen  yrs_worked_64_65 = mean(yrs_worked)
drop yrs_worked 

* COPY PASTE INTO APPENDIX TABLE ON SS
//table  educ_group if (age==64 | age==65), statistic(mean yrs_worked_64_65)


** combine Social Security retirement income (RwISRET) with pensions and annuities income (RwIPENA). 
** We keep only individuals who are retired and aged 65+. 
** We then run an OLS regression of this on the total number of self-reported years worked (RwJYEARS) and income at age 60-64.
keep if age>65

gen SS_income = isretr+ ipenar
keep if lbrfr==5 // keep if retired 

order age ID age race_use married SS_income isretr ipenar work_yrs_r

* we care about years worked at age 65
* for each ID, we choose their experience at the first year observed after 65, if getting SS
sort ID wave
replace work_yrs_r = work_yrs_r[_n-1] if ID==ID[_n-1] 


gen work_yrs_r_cat = 1 if work_yrs_r>=0 & work_yrs_r<30
replace work_yrs_r_cat = 2 if work_yrs_r>=30 & work_yrs_r<38
replace work_yrs_r_cat = 3 if work_yrs_r>=38 & work_yrs_r!=. //& work_yrs_r<43

reg SS_income i.race_use i.work_yrs_r_cat //[aweight=wtcrnhr]

*only count each person once. collapse by ID
collapse (mean) wage_60_64 yrs_worked_64_65 SS_income isretr  (min) race_use work_yrs_r_cat work_yrs_r, by(ID)


drop if SS_income<0 | SS_income==.
egen tercile=xtile(SS_income), by(race_use work_yrs_r_cat) n(3) 

collapse (p50) SS_income , by(race_use work_yrs_r_cat tercile)
drop if race_use==.


* Table 125 numbers - copy paste
preserve 
reshape wide SS_income, i(race_use work_yrs_r_cat) j(tercile) // for table in appendix
restore

sort race_use tercile work_yrs_r_cat
drop  tercile work_yrs_r_cat
replace SS_income =SS_income/5200
preserve
keep if race_use==2 // blacks
drop race_use
outsheet using "${data_model}/Blacks/SS_inc.txt", nolabel nonames replace	
restore 

keep if race_use==3  // hispanics
drop race_use
outsheet using "${data_model}/Hispanics/SS_inc.txt", nolabel nonames replace	
}

	
* SPOUSES' INCOME 	- RACIAL MINORITIES 
qui{
clear 
use "${data}/HRS_Analysis.dta"
*keep alive and dead: 1 and 4 are alive and 5 and 6 are dead
keep if iwstatr==1 | iwstatr==4 
* keep Whites
keep if hispanic==1 | race==2 // 

gen race_use=2 if race==2
replace race_use=3 if hispanic==1 

gen educ_group=1 if raedyrs <=12
replace educ_group=2 if raedyrs>=13 & raedyrs<16
replace educ_group=3 if raedyrs>=16 & raedyrs!=.

keep if gender==0  // keep males 0=male, 1=women
keep if educ_group==1

keep if age>65 & married==1
gen SS_income_spouse = isrets+ ipenas


collapse (mean) SS_income_spouse (min) race_use , by(ID)

table race_use, stat(mean SS_income_spouse) stat(p50 SS_income_spouse)
collapse (mean) SS_income_spouse, by(race_use)

replace SS_income_spouse=SS_income_spouse/5200 //  model units


preserve 
keep if race_use==2 
drop race_use
outsheet using "${data_model}/Blacks\Income_spouse_ret.txt", nolabel nonames replace	
restore 

keep if race_use==3 
drop race_use
outsheet using "${data_model}/Hispanics/Income_spouse_ret.txt", nolabel nonames replace	
}



* spouse's OOP
* Table 92 - bottom panel 
qui{
clear 
use "${data}/HRS_Analysis.dta"


qui{
sort ID wave
gen OOP_current=.
replace OOP_current = oopmdr[_n+1] if ID==ID[_n+1] & wave==wave[_n+1]-1 // OOP are from the past 2 years, so we use the observation from the next wave to infer OOP in next 2 years
gen OOP_current_s = oopmds[_n+1] if ID==ID[_n+1] & wave==wave[_n+1]-1 
}

*keep alive and dead: 1 and 4 are alive and 5 and 6 are dead
keep if iwstatr==1 | iwstatr==4 
* keep Whites
drop if hispanic==1 // drop Hispanics
drop if race==2 // drop Blacks

gen educ_group=1 if raedyrs <=12
replace educ_group=2 if raedyrs>=13 & raedyrs<16
replace educ_group=3 if raedyrs>=16 & raedyrs!=.

keep if gender==0  // keep males 0=male, 1=women

keep if age>65 & married==1

table age, stat(mean OOP_current_s)

gen age_group=65 if age>=65 & age<=69
replace age_group=70 if age>=70 & age<=74
replace age_group=75 if age>=75 & age<=79
replace age_group=80 if age>=80 & age<=84
replace age_group=85 if age>=85 & age<=89
replace age_group=90 if age>=90 & age<=100

drop if age_group==.

replace OOP_current_s=OOP_current_s/2
* numbers for Table 92
table age_group, stat(mean OOP_current_s)
}


* spouse's OOP - RACIAL MINORITIES
qui{
clear 
use "${data}/HRS_Analysis.dta"


* OOP in the next 2 years
* OOP
qui{
sort ID wave
gen OOP_current=.
replace OOP_current = oopmdr[_n+1] if ID==ID[_n+1] & wave==wave[_n+1]-1 // OOP are from the past 2 years, so we use the observation from the next wave to infer OOP in next 2 years
gen OOP_current_s = oopmds[_n+1] if ID==ID[_n+1] & wave==wave[_n+1]-1 
}

*keep alive and dead: 1 and 4 are alive and 5 and 6 are dead
keep if iwstatr==1 | iwstatr==4 

//keep if hispanic==1 | race==2 // 
gen race_use=2 if race==2
replace race_use=3 if hispanic==1 
replace race_use=1 if race!=2 & race!=3

gen educ_group=1 if raedyrs <=12
replace educ_group=2 if raedyrs>=13 & raedyrs<16
replace educ_group=3 if raedyrs>=16 & raedyrs!=.
keep if educ_group==1

keep if gender==0  // keep males 0=male, 1=women

keep if age>=65 & married==1

table age, stat(mean OOP_current_s)

gen age_group=65 if age>=65 & age<=69
replace age_group=70 if age>=70 & age<=74
replace age_group=75 if age>=75 & age<=79
replace age_group=80 if age>=80 & age<=84
replace age_group=85 if age>=85 & age<=89
replace age_group=90 if age>=90 & age<=100

drop if age_group==.

replace OOP_current_s=OOP_current_s/2
table race_use age_group, stat(mean OOP_current_s)
drop if race_use==1

insobs 3
replace age=95 if age==. & age[_n-1]!=.
replace age=97 if age==95 & age[_n-1]==95 & gender==.
replace age=98 if age==95 & age[_n-1]==97 & gender==.


fillin age race_use
tab age
gen age_sq=age^2

reg OOP_current_s age age_sq if race_use==2
predict OOP_wife2 if race_use==2
reg OOP_current_s age age_sq if race_use==3
predict OOP_wife3 if race_use==3
gen OOP_wife=OOP_wife2 if race_use==2
replace OOP_wife=OOP_wife3 if race_use==3
drop OOP_wife2  OOP_wife3
 
	collapse OOP_wife, by(age race_use)
	drop if race_use==.
	replace OOP_wife=OOP_wife/5200 // model units
	
	preserve
	keep if race_use==2 
drop race_use
	sort  age 
drop  age
outsheet using "${data_model}/Blacks/OOP_spouse_old.txt", nolabel nonames replace	
restore 

	keep if race_use==3 
drop race_use
	sort  age 
drop  age
outsheet using "${data_model}/Hispanics/OOP_spouse_old.txt", nolabel nonames replace	
}


